/*  
 * @(#) BaseDaoImpl.java Create on 2012-5-8 下午4:29:42   
 *   
 * Copyright 2012 by xl.   
 */

package cn.com.common.base.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.Collections;
import java.util.List;
import java.util.Map;

import javax.annotation.Resource;

import org.apache.commons.beanutils.BeanUtils;
import org.apache.commons.lang.StringUtils;
import org.apache.commons.lang.math.NumberUtils;
import org.apache.log4j.Logger;
import org.springframework.dao.EmptyResultDataAccessException;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.PreparedStatementCreator;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.jdbc.core.namedparam.MapSqlParameterSource;
import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;
import org.springframework.jdbc.support.GeneratedKeyHolder;
import org.springframework.jdbc.support.KeyHolder;

import cn.com.common.base.bean.PageBean;

/**
 * 
 * @author zhanghongliang
 * @date 2012-5-8
 */
public abstract class BaseDaoImpl implements BaseDao {
	protected Logger log = Logger.getLogger(this.getClass());

	@Resource
	SqlHelper sqlHelper;

	protected abstract NamedParameterJdbcTemplate getNamedJdbc();

	public <T> T get(long id, Class<T> clazz) throws Exception {
		if (clazz == null) {
			log.warn("传入参数错误 clazz is null");
			return null;
		}

		String tableName = BeanUtils.getProperty(clazz.newInstance(), SqlHelper.TABLE_NAME_KEY);
		if (StringUtils.isBlank(tableName)) {
			log.warn("传入参数错误 clazz bean." + SqlHelper.TABLE_NAME_KEY + " is null");
			return null;
		}
		String sql = "SELECT * FROM " + tableName + " WHERE id=" + id;
		return executeGet(sql, clazz);
	}

	public <T> T get(long id, RowMapper<T> row, String tableName) throws Exception {
		if (StringUtils.isBlank(tableName)) {
			log.warn("传入参数错误 tableName is null");
			return null;
		}
		String sql = "SELECT * FROM " + tableName + " WHERE id=" + id;
		return this.executeGet(sql, row);
	}

	public <T> T executeGet(String sql, RowMapper<T> row, Object... objs) throws Exception {
		if (row == null) {
			log.warn("传入参数错误 rowMapper is null");
			return null;
		}

		if (log.isDebugEnabled()) {
			log.debug("get sql:" + sql);
		}
		try {
			return getNamedJdbc().getJdbcOperations().queryForObject(sql, row, objs);
		} catch (EmptyResultDataAccessException e) {
		}
		return null;
	}

	public <T> T executeGet(String sql, Class<T> clazz, Object... objs) throws Exception {
		if (clazz == null) {
			log.warn("executeGet--> 传入参数Class is null");
			return null;
		}
		if (log.isDebugEnabled()) {
			log.debug("get sql:" + sql);
		}
		RowMapper<T> rowMapper = new BeanPropertyRowMapper<T>(clazz);
		return executeGet(sql, rowMapper, objs);
	}

	public long insert(Object obj, boolean generatedFlag) throws Exception {
		if (obj == null) {
			log.warn("inert object is null");
			return 0;
		}

		@SuppressWarnings("unchecked")
		Map<String, Object> params = BeanUtils.describe(obj);

		String sql = sqlHelper.getInsertSql(params);
		if (StringUtils.isBlank(sql)) {
			log.warn("inert sql is null,please check Object " + SqlHelper.TABLE_NAME_KEY);
			return 0;
		}

		return this.executeInsert(sql, params, generatedFlag);
	}

	public long insert(Map<String, Object> params, boolean generatedFlag) throws Exception {
		if (params == null || params.size() < 0) {
			log.warn("inert map is null");
			return 0;
		}
		String sql = sqlHelper.getInsertSql(params);
		if (StringUtils.isBlank(sql)) {
			log.warn("inert sql is null,please check Object " + SqlHelper.TABLE_NAME_KEY);
			return 0;
		}

		return executeInsert(sql, params, generatedFlag);
	}

	public long executeInsert(String sql, Map<String, Object> params, boolean generatedFlag) throws Exception {

		if (StringUtils.isBlank(sql)) {
			log.warn("sql is null");
			return 0;
		}
		NamedParameterJdbcTemplate namedJabc = this.getNamedJdbc();
		if (namedJabc == null) {
			log.warn("JdbcTemplate is null");
			return 0;
		}

		Map<String, Object> _params = params;
		if (_params == null) {
			_params = Collections.emptyMap();
		}

		if (log.isDebugEnabled()) {
			log.debug("insert sql:" + sql);
		}

		if (generatedFlag) {
			KeyHolder keyHolder = new GeneratedKeyHolder();
			namedJabc.update(sql, new MapSqlParameterSource(_params), keyHolder);
			long lastInsertId = keyHolder.getKey().longValue();
			return lastInsertId;
		} else {
			return namedJabc.update(sql, _params);
		}
	}

	public long executeInsert(final String sql, List<Object> params, boolean generatedFlag) throws Exception {
		if (StringUtils.isBlank(sql)) {
			log.warn("sql is null");
			return 0;
		}
		NamedParameterJdbcTemplate namedJabc = this.getNamedJdbc();
		if (namedJabc == null) {
			log.warn("JdbcTemplate is null");
			return 0;
		}

		if (log.isDebugEnabled()) {
			log.debug("insert sql:" + sql);
		}

		final List<Object> _params = params == null ? Collections.emptyList() : params;
		if (generatedFlag) {
			KeyHolder keyHolder = new GeneratedKeyHolder();
			namedJabc.getJdbcOperations().update(new PreparedStatementCreator() {
				public PreparedStatement createPreparedStatement(Connection con) throws SQLException {
					PreparedStatement result = con.prepareStatement(sql, PreparedStatement.RETURN_GENERATED_KEYS);
					int index = 1;
					for (Object object : _params) {
						result.setObject(index, object);
						index++;
					}
					return result;
				}
			}, keyHolder);
			long lastInsertId = keyHolder.getKey().longValue();
			return lastInsertId;
		} else {
			return namedJabc.getJdbcOperations().update(sql, _params);
		}
	}

	public int update(Object obj) throws Exception {
		if (obj == null) {
			log.warn("update object is null");
			return 0;
		}

		@SuppressWarnings("unchecked")
		Map<String, Object> params = BeanUtils.describe(obj);
		String sql = sqlHelper.getUpdateSql(params);
		return this.executeUpdate(sql, params);
	}

	public int updateNotNull(Object obj) throws Exception {
		if (obj == null) {
			log.warn("update object is null");
			return 0;
		}

		@SuppressWarnings("unchecked")
		Map<String, Object> params = BeanUtils.describe(obj);
		String sql = sqlHelper.getUpdateNotNullSql(params);
		return this.executeUpdate(sql, params);
	}

	public int executeUpdate(String sql, Map<String, Object> parmas) throws Exception {
		if (StringUtils.isBlank(sql)) {
			log.warn("sql is null");
			return 0;
		}
		NamedParameterJdbcTemplate namedJabc = this.getNamedJdbc();
		if (namedJabc == null) {
			log.warn("JdbcTemplate is null");
			return 0;
		}

		Map<String, Object> _params = parmas;
		if (_params == null) {
			_params = Collections.emptyMap();
		}

		if (log.isDebugEnabled()) {
			log.debug("update sql:" + sql);
		}
		return namedJabc.update(sql, _params);
	}

	public int executeUpdate(String sql, List<Object> params) throws Exception {
		if (StringUtils.isBlank(sql)) {
			log.warn("sql is null");
			return 0;
		}
		NamedParameterJdbcTemplate namedJabc = this.getNamedJdbc();
		if (namedJabc == null) {
			log.warn("JdbcTemplate is null");
			return 0;
		}
		List<Object> _params = params == null ? Collections.emptyList() : params;
		if (log.isDebugEnabled()) {
			log.debug("update sql:" + sql);
		}
		return namedJabc.getJdbcOperations().update(sql, _params.toArray());
	}

	public int delete(Object obj) throws Exception {
		if (obj == null) {
			log.warn("delete object is null");
			return 0;
		}
		NamedParameterJdbcTemplate namedJabc = this.getNamedJdbc();
		if (namedJabc == null) {
			log.warn("JdbcTemplate is null");
			return 0;
		}
		String tableName = BeanUtils.getProperty(obj, SqlHelper.TABLE_NAME_KEY);
		if (StringUtils.isBlank(tableName)) {
			log.warn("delete object has't " + SqlHelper.TABLE_NAME_KEY + " property");
			return 0;
		}
		Object id = BeanUtils.getProperty(obj, "id");
		if (id == null) {
			log.warn("delete object has't id property");
			return 0;
		}

		String sql = "DELETE FROM " + tableName + " WHERE id=?";
		if (log.isDebugEnabled()) {
			log.debug("delete sql=" + sql);
		}
		return namedJabc.getJdbcOperations().update(sql, id);
	}

	@SuppressWarnings("unchecked")
	public <T> List<T> list(Object obj, Class<T> clazz) throws Exception {
		Map<String, Object> params = null;
		if (obj == null) {
			params = Collections.emptyMap();
		} else {
			params = BeanUtils.describe(obj);
		}
		return list(params, clazz);
	}

	@SuppressWarnings("unchecked")
	public <T> List<T> list(Object obj, RowMapper<T> rowMapper) throws Exception {
		Map<String, Object> params = null;
		if (obj == null) {
			params = Collections.emptyMap();
		} else {
			params = BeanUtils.describe(obj);
		}
		return list(params, rowMapper);
	}

	public <T> List<T> list(Map<String, Object> params, Class<T> clazz) throws Exception {
		if (clazz == null) {
			log.warn("getList clazz is null");
			return Collections.emptyList();
		}

		return list(params, new BeanPropertyRowMapper<T>(clazz));
	}

	public <T> List<T> list(Map<String, Object> params, RowMapper<T> rowMapper) throws Exception {
		if (params == null) {
			log.warn("getList map is null");
			return Collections.emptyList();
		}
		if (rowMapper == null) {
			log.warn("getList rowMapper is null");
			return Collections.emptyList();
		}

		String sql = sqlHelper.getQueryListSql(params);

		return executeQueryForList(sql, params, rowMapper);
	}

	public <T> List<T> queryForList(String sql, Map<String, Object> params, Class<T> clazz) throws Exception {
		if (clazz == null) {
			log.warn("getList class is null");
			return Collections.emptyList();
		}

		return executeQueryForList(sql, params, new BeanPropertyRowMapper<T>(clazz));
	}

	public <T> List<T> executeQueryForList(String sql, Map<String, Object> params, RowMapper<T> rowMapper) throws Exception {
		if (params == null) {
			params = Collections.emptyMap();
		}
		Object pageNow = params.get("_pageNow");
		Object pageSize = params.get("_pageSize");

		int _pn = 0;
		int _ps = 0;

		if (pageNow != null && pageSize != null) {
			_pn = NumberUtils.toInt(pageNow.toString(), 0);
			_ps = NumberUtils.toInt(pageSize.toString(), PageBean.DEFAULT_PAGE_SIZE);
		}
		
		String order = (String) params.get("_order");
		if(StringUtils.isNotBlank(order)){
			sql += " ORDER BY " + order;
		}else{
			sql += " ORDER BY ID DESC";
		}
		return executeQueryForList(sql, params, rowMapper, _ps, _pn);
	}

	public <T> List<T> executeQueryForList(String sql, Map<String, Object> params, RowMapper<T> rowMapper, int pageSize, int pageNow)
			throws Exception {
		if (StringUtils.isBlank(sql)) {
			log.warn("list sql is null");
			return Collections.emptyList();
		}
		if (rowMapper == null) {
			log.warn("list rowMapper is null");
			return Collections.emptyList();
		}
		NamedParameterJdbcTemplate namedJabc = this.getNamedJdbc();
		if (namedJabc == null) {
			log.warn("JdbcTemplate is null");
			return Collections.emptyList();
		}

		if (params == null) {
			params = Collections.emptyMap();
		}

		sql += sqlHelper.getQueryCondtion(params);

		sql = sqlHelper.getFetchSql(sql, pageNow, pageSize);
		if (log.isDebugEnabled()) {
			log.debug("list sql:" + sql);
		}

		return namedJabc.query(sql, params, rowMapper);
	}

	public <T> List<T> executeQueryForList(String sql, List<Object> params, RowMapper<T> rowMapper, int pageNow, int pageSize) throws Exception {
		if (StringUtils.isBlank(sql)) {
			log.warn("list sql is null");
			return Collections.emptyList();
		}
		if (rowMapper == null) {
			log.warn("list rowMapper is null");
			return Collections.emptyList();
		}
		NamedParameterJdbcTemplate namedJabc = this.getNamedJdbc();
		if (namedJabc == null) {
			log.warn("JdbcTemplate is null");
			return Collections.emptyList();
		}

		if (params == null) {
			params = Collections.emptyList();
		}
		sql = sqlHelper.getFetchSql(sql, pageNow, pageSize);
		if (log.isDebugEnabled()) {
			log.debug("list sql:" + sql);
		}

		return namedJabc.getJdbcOperations().query(sql, rowMapper, params.toArray());
	}

	public long queryForLong(Object obj) throws Exception {
		if (obj == null) {
			log.warn("queryForLong object is null");
			return 0;
		}
		@SuppressWarnings("unchecked")
		Map<String, Object> params = BeanUtils.describe(obj);
		return queryForLong(params);
	}

	public long queryForLong(Map<String, Object> params) throws Exception {
		if (params == null) {
			log.warn("queryForLong map is null");
			return 0;
		}
		String sql = sqlHelper.getQueryCountSql(params);
		return executeQueryForLong(sql, params);
	}

	public long executeQueryForLong(String sql, Map<String, Object> params) throws Exception {
		if (StringUtils.isBlank(sql)) {
			log.warn("QueryForLong sql is null");
			return 0;
		}

		NamedParameterJdbcTemplate namedJabc = this.getNamedJdbc();
		if (namedJabc == null) {
			log.warn("JdbcTemplate is null");
			return 0;
		}
		if (params == null) {
			params = Collections.emptyMap();
		}
		sql += sqlHelper.getQueryCondtion(params);
		return namedJabc.queryForLong(sql, params);
	}

	public long executeQueryForLong(String sql, List<Object> params) throws Exception {
		if (StringUtils.isBlank(sql)) {
			log.warn("QueryForLong sql is null");
			return 0;
		}

		NamedParameterJdbcTemplate namedJabc = this.getNamedJdbc();
		if (namedJabc == null) {
			log.warn("JdbcTemplate is null");
			return 0;
		}
		if (params == null) {
			params = Collections.emptyList();
		}
		return namedJabc.getJdbcOperations().queryForLong(sql, params);
	}

	@SuppressWarnings("unchecked")
	public <T> PageBean<T> getPageBean(Object obj, Class<T> clazz) throws Exception {
		Map<String, Object> params = null;
		if (obj == null) {
			params = Collections.emptyMap();
		} else {
			params = BeanUtils.describe(obj);
		}

		return getPageBean(params, clazz);
	}

	public <T> PageBean<T> getPageBean(Map<String, Object> params, Class<T> clazz) throws Exception {
		RowMapper<T> rowMapper = null;
		if (clazz != null) {
			rowMapper = new BeanPropertyRowMapper<T>(clazz);
		}
		return getPageBean(params, rowMapper);
	}

	public <T> PageBean<T> getPageBean(Map<String, Object> params, RowMapper<T> rowMapper) throws Exception {
		if (params == null) {
			params = Collections.emptyMap();
		}
		String tableName = (String) params.get(SqlHelper.TABLE_NAME_KEY);
		String sql = "";
		String sqlCount = "";
		int pageSize = 0;
		int pageNow = 0;
		if (StringUtils.isNotBlank(tableName)) {
			sql = "SELECT * FROM " + tableName + " WHERE 1=1 ";
			sqlCount = "SELECT COUNT(*) FROM " + tableName + " WHERE 1=1 ";

			Object _ps = params.get("_pageSize");
			Object _pn = params.get("_pageNow");

			if (_pn != null) {
				pageNow = NumberUtils.toInt(_pn.toString());

				if(_ps!=null){
					pageSize = NumberUtils.toInt(_ps.toString(), 0);
				}
				if(pageSize==0){
					pageSize = PageBean.DEFAULT_PAGE_SIZE;
				}
			}
		}
		
		return queryForPage(sql, sqlCount, params, rowMapper, pageNow, pageSize);
	}

	public <T> PageBean<T> queryForPage(String sql, String sqlCount, Map<String, Object> params, RowMapper<T> rowMapper, int pageNow, int pageSize)
			throws Exception {
		PageBean<T> result = new PageBean<T>();
		result.setPageNow(pageNow);
		result.setPageSize(pageSize);
		if (StringUtils.isBlank(sql)) {
			log.warn("queryForPage sql is null");
			return result;
		}
		if (StringUtils.isBlank(sqlCount)) {
			log.warn("queryForPage sqlCount is null");
			return result;
		}

		if (rowMapper == null) {
			log.warn("queryForPage rowMpper is null");
			return result;
		}
		NamedParameterJdbcTemplate namedJabc = this.getNamedJdbc();
		if (namedJabc == null) {
			log.warn("JdbcTemplate is null");
			return result;
		}

		String condtion = sqlHelper.getQueryCondtion(params);
		String fecthSql = sql + condtion;
		String fetchSqlCount  = sqlCount + condtion;

		String order = (String) params.get("_order");
		if(StringUtils.isNotBlank(order)){
			fecthSql += " ORDER BY " + order;
		}else{
			fecthSql += " ORDER BY ID DESC";
		}
		
		
		fecthSql = sqlHelper.getFetchSql(fecthSql, pageNow, pageSize);

		if (log.isDebugEnabled()) {
			log.debug("sql:" + fecthSql);
			log.debug("sqlCount:" + fetchSqlCount);
		}
		if (params == null) {
			params = Collections.emptyMap();
		}
		List<T> rows = namedJabc.query(fecthSql, params, rowMapper);

		if(pageNow>1 && rows.size()==0){
			return queryForPage(sql,sqlCount,  params, rowMapper, pageNow-1,pageSize);
		}
		long total = namedJabc.queryForLong(fetchSqlCount, params);
		result.setRows(rows);
		result.setTotal((int) total);
		return result;
	}

	public <T> PageBean<T> queryForPage(String sql, String sqlCount, List<Object> params, Class<T> clazz, int pageNow, int pageSize) throws Exception {
		RowMapper<T> rowMapper = null;
		if (clazz != null) {
			rowMapper = new BeanPropertyRowMapper<T>(clazz);
		}
		return queryForPage(sql, sqlCount, params, rowMapper, pageNow, pageSize);
	}

	public <T> PageBean<T> queryForPage(String sql, String sqlCount, List<Object> params, RowMapper<T> rowMapper, int pageNow, int pageSize)
			throws Exception {
		PageBean<T> result = new PageBean<T>();
		result.setPageNow(pageNow);
		result.setPageSize(pageSize);
		if (StringUtils.isBlank(sql)) {
			log.warn("queryForPage sql is null");
			return result;
		}
		if (StringUtils.isBlank(sqlCount)) {
			log.warn("queryForPage sqlCount is null");
			return result;
		}

		if (rowMapper == null) {
			log.warn("queryForPage rowMpper is null");
			return result;
		}
		NamedParameterJdbcTemplate namedJabc = this.getNamedJdbc();
		if (namedJabc == null) {
			log.warn("JdbcTemplate is null");
			return result;
		}

		sql = sqlHelper.getFetchSql(sql, pageNow, pageSize);
		long total = namedJabc.getJdbcOperations().queryForLong(sqlCount, params);
		List<T> rows = namedJabc.getJdbcOperations().query(sql, rowMapper, params.toArray());

		result.setRows(rows);
		result.setTotal((int) total);
		return result;
	}
}
